using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public FilialVO getFilial(int? idFilial, string filial)
		{
			FilialVO filialVO = null;
			List<FilialVO> listFilialVOs = getFilials(idFilial, filial, null);
			if (listFilialVOs != null && listFilialVOs.Count > 0)
			{
				filialVO = listFilialVOs[0];
			}
			return filialVO;
		}

		public FilialVO getFilial(int? idFilial)
		{
			return getFilial(idFilial, null);
		}

		public List<FilialVO> getFilials()
		{
			return getFilials(null, null, null);
		}

		public List<FilialVO> getFilials(int? idFilial)
		{
			return getFilials(idFilial, null, null);
		}

		public List<FilialVO> getFilials(int? idFilial, string filial)
		{
			return getFilials(idFilial, filial, null);
		}

		public List<FilialVO> getFilials(int? idFilial, string filial, int? idEmpresa)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT fil.idFilial as fil_idFilial, fil.filial as fil_filial, fil.idEmpresa as fil_idEmpresa, emp.empresa as emp_empresa, fil.matriz as fil_matriz, fil.Cadastro as fil_Cadastro, fil.CadastradoPor as fil_CadastradoPor, fil.Edicao as fil_Edicao, fil.EditadoPor as fil_EditadoPor  ");
			query.AppendLine("FROM filial as fil LEFT OUTER JOIN empresa as emp on fil.idEmpresa=emp.idEmpresa  ");
			if (idFilial != null)
			{
				query.AppendLine(where + "fil.idFilial=@idFilial ");
				where = "AND ";
			}
			if (filial != null)
			{
				query.AppendLine(where + "fil.filial LIKE @filial ");
				where = "AND ";
			}
			if (idEmpresa != null)
			{
				query.AppendLine(where + "fil.idEmpresa=@idEmpresa ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY fil.filial ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (filial!= null) cmd.Parameters.Add(getParameter(cmd, "@filial", "%"+filial+"%"));
			if (idEmpresa!= null) cmd.Parameters.Add(getParameter(cmd, "@idEmpresa", idEmpresa));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<FilialVO> listFilial = new List<FilialVO>();

				while (reader.Read())
				{
					FilialVO filialVO = FilialVO.CreateVO();
					filialVO.idFilial = reader.GetInt32(reader.GetOrdinal("fil_idFilial"));
					filialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
					filialVO.idEmpresa = reader.GetInt32(reader.GetOrdinal("fil_idEmpresa"));
					if (!reader.IsDBNull(reader.GetOrdinal("emp_empresa")))
						filialVO.idEmpresaVO.empresa = reader.GetString(reader.GetOrdinal("emp_empresa"));
					filialVO.matriz = reader.GetString(reader.GetOrdinal("fil_matriz")).Equals("S");
					filialVO.cadastro = reader.GetDateTime(reader.GetOrdinal("fil_Cadastro"));
					filialVO.cadastradoPor = reader.GetString(reader.GetOrdinal("fil_CadastradoPor"));
					filialVO.edicao = reader.GetDateTime(reader.GetOrdinal("fil_Edicao"));
					filialVO.editadoPor = reader.GetString(reader.GetOrdinal("fil_EditadoPor"));


					listFilial.Add(filialVO);
				}
				reader.Close();
				return listFilial;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setFilial(FilialVO filialVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE filial ");
			query.AppendLine("SET filial=@filial, idEmpresa=@idEmpresa, matriz=@matriz,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idFilial=@idFilial ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idFilial", filialVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@filial", filialVO.filial));
			cmd.Parameters.Add(getParameter(cmd, "@idEmpresa", filialVO.idEmpresa));
			cmd.Parameters.Add(getParameter(cmd, "@matriz", (filialVO.matriz==null?" ":(filialVO.matriz.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", filialVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", filialVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", filialVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", filialVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newFilial(FilialVO filialVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO filial ");
			query.AppendLine("  ( filial, idEmpresa, matriz, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @filial, @idEmpresa, @matriz, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@filial", filialVO.filial));
			cmd.Parameters.Add(getParameter(cmd, "@idEmpresa", filialVO.idEmpresa));
			cmd.Parameters.Add(getParameter(cmd, "@matriz", (filialVO.matriz==null?" ":(filialVO.matriz.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", filialVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", filialVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", filialVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", filialVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('filial') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delFilial(FilialVO filialVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM filial ");
			query.AppendLine("WHERE idFilial=@idFilial ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idFilial", filialVO.idFilial));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountFilialxIdEmpresa(int? idEmpresa)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idEmpresa) as ocorrencias FROM filial WHERE IdEmpresa=@IdEmpresa ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdEmpresa", idEmpresa));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
